package com.ef.dao.impl;

import java.util.List;

import org.hibernate.SQLQuery;

import com.ef.dao.LeadsDao;
import com.ef.domain.Leads;

public class LeadsDaoImpl extends DaoImpl<Leads> implements LeadsDao {
	@SuppressWarnings("unchecked")
	// 导出Pte的业绩
	public List<Object> exportPteResults(String start, String end,
			String tmList, Long selMarketingPolicyId, String selleadsType,
			Long selAdChannelId, Long selSchoolId) {
		StringBuffer sql = new StringBuffer(
				"select g.name as CC, g.worknumber as ID, a.name, a.mobiletelephone, "
						+ " to_char(a.initvisittime,'yyyy-MM-dd') as initvisittime, "
						+ "d.name as PAC, e.name as marketing,  c.name as schoolname, a.leadstype as studenttype, a.comments, f.name as leadsStatus ,f.statusprop "
						+ "       from ef_leads a,   ef_school c, ef_marketingpolicy d, ef_adchannel e ,ef_leadsstatus f, ef_PTE g"
						+ "       where  a.status='VALID' and a.school_id=c.id(+) and a.pte_id=g.id "
						+ "      and a.marketingpolicy_id=d.id(+) and a.adchannel_id=e.id(+) and a.leadsstatusex_id=f.id");
		if (end != null && !end.equals("")) {
			sql.append(" and initvisittime<=to_date('");
			sql.append(end);
			sql.append("', 'yyyy-MM-dd')");
		}
		if (start != null && !start.equals("")) {
			sql.append(" and initvisittime>=to_date('");
			sql.append(start);
			sql.append("', 'yyyy-MM-dd')");
		}
		if (tmList != null) {
			sql.append(" and a.pte_id in (");
			sql.append(tmList);
			sql.append(")");
		}
		if (selMarketingPolicyId != null) {
			sql.append(" and a.marketingpolicy_id=");
			sql.append(selMarketingPolicyId);
			sql.append("  ");
		}
		if (selAdChannelId != null) {
			sql.append(" and a.adchannel_id=");
			sql.append(selAdChannelId);
			sql.append("  ");
		}
		if (selSchoolId != null) {
			sql.append(" and a.school_id=");
			sql.append(selSchoolId);
			sql.append("  ");
		}
		if (selleadsType != null && selleadsType.trim().length() > 0) {
			sql.append(" and a.leadstype='");
			sql.append(selleadsType.trim());
			sql.append("'");
		}
		sql.append(" order by pte_id");
		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		query.addScalar("CC");
		query.addScalar("ID");
		query.addScalar("name");
		query.addScalar("mobiletelephone");
		// query.addScalar("createtime");
		query.addScalar("initvisittime");
		query.addScalar("PAC");
		query.addScalar("marketing");
		query.addScalar("schoolname");
		query.addScalar("studenttype");
		query.addScalar("comments");
		query.addScalar("leadsStatus");
		query.addScalar("statusprop");
		System.out.println(sql.toString());
		List<Object> list = query.list();
		return list;
	}

	// 导出Pte的业绩
	public List<Object> exportPteResults(String start, String end,
			Long selMarketingPlanId,Long selCreateUserId,Long teamid) {
		StringBuffer sql = new StringBuffer(
				"select  PNAME,PNO, LNAME,LNO, MARKETPLANNAME,NAME, LEADSSTATUS,INVALID,VALID,NOANSWER,SHOWUP,PAY ,CREATOR,CREATETIME,TMNAME FROM view_pteresult where PID is not null ");
		if (start != null && !start.equals("")) {
			sql.append(" and STARTDATE>=to_date('");
			sql.append(start);
			sql.append("', 'yyyy-MM-dd')");
		}
		if (end != null && !end.equals("")) {
			sql.append(" and ENDDATE<=to_date('");
			sql.append(end);
			sql.append("', 'yyyy-MM-dd')");
		}

		if (selMarketingPlanId != null) {
			sql.append(" and MARKETPLANID=");
			sql.append(selMarketingPlanId);
			sql.append("  ");
		}
		if (selCreateUserId != null) {
			sql.append(" and CREATORID=");
			sql.append(selCreateUserId);
			sql.append("  ");
		}
		if (teamid != null) {
			sql.append(" and teamid=");
			sql.append(teamid);
			sql.append("  ");
		}
		sql.append(" order by PID,MARKETPLANID");
		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		query.addScalar("PNAME");
		query.addScalar("PNO");
		query.addScalar("LNAME");
		query.addScalar("LNO");
		query.addScalar("MARKETPLANNAME");
		query.addScalar("NAME");
		query.addScalar("LEADSSTATUS");
		query.addScalar("INVALID");
		query.addScalar("VALID");
		query.addScalar("NOANSWER");
		query.addScalar("SHOWUP");
		query.addScalar("PAY");
		query.addScalar("CREATOR");
		query.addScalar("CREATETIME");
		query.addScalar("TMNAME");
		System.out.println(sql.toString());
		List<Object> list = query.list();
		return list;
	}

	// 导出Pte的业绩
	public List<Object> exportPteLeadResults(String start, String end,
			Long selMarketingPlanId,Long selCreateUserId,Long teamid) {
		StringBuffer sql = new StringBuffer(
				"select  LNAME,LNO, PNAME,PNO, MARKETPLANNAME,NAME,LEADSSTATUS,INVALID,VALID,NOANSWER,SHOWUP,PAY  ,CREATOR,CREATETIME,TMNAME FROM view_pteresult where LID is not null ");
		if (start != null && !start.equals("")) {
			sql.append(" and STARTDATE>=to_date('");
			sql.append(start);
			sql.append("', 'yyyy-MM-dd')");
		}
		if (end != null && !end.equals("")) {
			sql.append(" and ENDDATE<=to_date('");
			sql.append(end);
			sql.append("', 'yyyy-MM-dd')");
		}

		if (selMarketingPlanId != null) {
			sql.append(" and MARKETPLANID=");
			sql.append(selMarketingPlanId);
			sql.append("  ");
		}
		if (selCreateUserId != null) {
			sql.append(" and CREATORID=");
			sql.append(selCreateUserId);
			sql.append("  ");
		}
		if (teamid != null) {
			sql.append(" and teamid=");
			sql.append(teamid);
			sql.append("  ");
		}
		sql.append(" order by LID,MARKETPLANID");
		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		query.addScalar("LNAME");
		query.addScalar("LNO");
		query.addScalar("PNAME");
		query.addScalar("PNO");
		query.addScalar("MARKETPLANNAME");
		query.addScalar("NAME");
		query.addScalar("LEADSSTATUS");
		query.addScalar("INVALID");
		query.addScalar("VALID");
		query.addScalar("NOANSWER");
		query.addScalar("SHOWUP");
		query.addScalar("PAY");
		query.addScalar("CREATOR");
		query.addScalar("CREATETIME");
		query.addScalar("TMNAME");
		System.out.println(sql.toString());
		List<Object> list = query.list();
		return list;
	}
}
//ef.sql